The field of the present disclosure relates generally to a multi-database query system and method and, more particularly, a computer-based system and method for storing and managing queries within a centralized environment for execution in a plurality of databases wherein the databases utilize more than one query language.
Databases are used to store information for an innumerable number of applications, including various commercial, industrial, technical, scientific and educational applications. For example, at least some financial transactions are performed over computer networks wherein transaction data such as products being purchased, purchase amounts, account information, etc. is transmitted over the computer network and stored within one or more databases. More specifically, a consumer may purchase a product or service from a merchant using a transaction card (e.g., credit card or debit card) over a multi-party interchange network wherein the transaction data including the consumer's name, card number, product or service being purchased, amount being paid, etc. is transmitted over the interchange network and stored within a variety of databases. This stored data may then be used by various parties involved in the process for reporting purposes.
Historically, the use of “charge” or transaction cards or payment cards for consumer transaction payments was at most regional and based on relationships between local credit or debit card issuing banks and various local merchants. The transaction card industry has since evolved with the issuing banks forming associations or networks (e.g., MasterCard®) and involving third party transaction processing companies (e.g., “Merchant Acquirers”) to enable cardholders to widely use transaction cards at any merchant's establishment, regardless of the merchant's banking relationship with the card issuer. (MasterCard is a registered trademark of MasterCard International Incorporated located in Purchase, New York).
For example, FIG. 1 of the present application shows an exemplary multi-party payment card industry system for enabling payment-by-card transactions in which the merchants and issuer do not need to have a one-to-one special relationship. In addition, various scenarios exist in the payment-by-card industry today, where the card issuer has a special or customized relationship with a specific merchant, or group of merchants. These special or customized relationships may, for example, include private label programs, co-brand programs, proprietary card brands, rewards programs, and others. As the payment card industry has evolved over the years, more and more consumers are using payment cards to purchase their goods and services. The more these cards are used, the more data that is processed and stored within the payment card systems. Parties involved in the payment process, such as an issuing bank, want to be able to retrieve and organize the data stored within the payment system.
As the reliance on such information increases, both the volume of information stored in most databases, as well as the number of users wishing to access that information, likewise increases. As the volume of information in a database increases, and the number of users wishing to access the database increases, the amount of computing resources required to manage such a database increases as well. Database management systems (DBMS's), which are computer programs that are used to access the information stored in databases, therefore often require tremendous resources to handle the heavy workloads placed on such systems. As such, significant resources have been devoted to increasing the performance of database management systems with respect to processing searches, or queries, to databases.
Improvements to both computer hardware and software have improved the capacities of conventional database management systems. For example, in the hardware area, increases in microprocessor performance, coupled with improved memory management systems, have improved the number of queries that a particular microprocessor can perform in a given unit of time. Furthermore, the use of multiple microprocessors and/or multiple networked computers has further increased the capacities of many database management systems.
From a software standpoint, the use of relational databases, which organize information into formally-defined tables, and which are typically accessed using a standardized language such as Structured Query Language (SQL), has substantially improved processing efficiency, as well as substantially simplified the creation, organization, and extension of information within a database. However, despite the existence of a language standard, some databases support non-standard language in the interest of offering additional features. Significant development efforts have been directed toward query “optimization”, whereby the execution of particular searches, or queries, is optimized in an automated manner to minimize the amount of resources required to execute each query. Some of these optimizations take advantage of non-standard language and thus produce query code that is not executable by other databases.
Through the incorporation of various hardware and software improvements, many high performance database management systems are able to handle hundreds or even thousands of queries each second, even on databases containing millions or billions of records. However, further increases in information volume and workload are inevitable, so continued advancements in database management systems are still required.
In addition, for various reasons, including security and performance concerns, data produced by an organization often resides in multiple databases. Each of these databases is generally considered disconnected from the other databases and has its own “namespace”. In such a case, a query executing in one database typically cannot access data stored in another database. Thus, the use of multiple, separate databases can also add an additional challenge to retrieving this data and creating reports from data that may be stored in different databases.
Many users require reports that include data stored in more than one database. Managing the creation, maintenance, and execution of multiple queries (one for each database) for a single user report introduces expense and duplication of logic in the queries. Further, generating a single report from queries in multiple databases can be difficult. While results from multiple queries may be sequentially appended to create a single report, a user may instead require a report based on an aggregation of data from multiple sources. For example, the user may require data from one source to be intermingled with data from another source, or the user may require a calculation based on data from more than one source.
Moreover, many queries require input parameters, such as a date range or a customer identifier. Accommodating these input parameters for one report across queries for multiple databases further increases the complexity and cost of report development and maintenance. Accordingly, a multi-language database query system is needed that prompts a user to input a plurality of parameter, generates a query using the parameters, and automatically deploys the query across multiple databases that support different languages so as to generate a report for the user.